package com.business.link;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.beans.link.Link;
import com.sql.DBFactory;

public class LinkListBus {
	private Connection conn = null;

	private Statement stmt = null;

	private ResultSet rs = null;

	private static final Log log = LogFactory.getLog(LinkListBus.class);

	@SuppressWarnings("unchecked")
	public List getLinkByUserName(String username) {
		List list = new ArrayList();
		String sql = "select * from link where username='" + username
				+ "'and isShow=1 order by link_id desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Link link = new Link();
				link.setIsShow(rs.getString("isShow"));
				link.setLinkDemo(rs.getString("link_demo"));
				link.setLinkId(rs.getString("link_id"));
				link.setLinkName(rs.getString("link_name"));
				link.setLinkNo(rs.getString("link_no"));
				link.setLinkUrl(rs.getString("link_url"));
				link.setUsername(rs.getString("username"));
				list.add(link);
			}
		} catch (Exception e) {
			log.error("the select link exception:" + e.getMessage());
			log.error("the select link sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List getAllLinkByUserName(String username) {
		List list = new ArrayList();
		String sql = "select * from link where username='" + username
				+ "' order by link_id desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Link link = new Link();
				link.setIsShow(rs.getString("isShow"));
				link.setLinkDemo(rs.getString("link_demo"));
				link.setLinkId(rs.getString("link_id"));
				link.setLinkName(rs.getString("link_name"));
				link.setLinkNo(rs.getString("link_no"));
				link.setLinkUrl(rs.getString("link_url"));
				link.setUsername(rs.getString("username"));
				list.add(link);
			}
		} catch (Exception e) {
			log.error("the select link exception:" + e.getMessage());
			log.error("the select link sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}

	public List getLinkTop5(String username) {

		return getLink(username, 5);
	}

	@SuppressWarnings("unchecked")
	public List getLink(String username, int number) {
		List list = new ArrayList();
		String sql = "select * from link where username='" + username
				+ "'and isShow=1 order by link_id desc limit 0," + number;
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Link link = new Link();
				link.setIsShow(rs.getString("isShow"));
				link.setLinkDemo(rs.getString("link_demo"));
				link.setLinkId(rs.getString("link_id"));
				link.setLinkName(rs.getString("link_name"));
				link.setLinkNo(rs.getString("link_no"));
				link.setLinkUrl(rs.getString("link_url"));
				link.setUsername(rs.getString("username"));
				list.add(link);
			}
		} catch (Exception e) {
			log.error("the select link exception:" + e.getMessage());
			log.error("the select link sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}

	public Link getLinkByNo(String linkNo) {
		Link link = new Link();
		String sql = "select * from link where link_no='" + linkNo+"'";

		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				link.setIsShow(rs.getString("isShow"));
				link.setLinkDemo(rs.getString("link_demo"));
				link.setLinkId(rs.getString("link_id"));
				link.setLinkName(rs.getString("link_name"));
				link.setLinkNo(rs.getString("link_no"));
				link.setLinkUrl(rs.getString("link_url"));
				link.setUsername(rs.getString("username"));

			}
		} catch (Exception e) {
			log.error("the select link exception:" + e.getMessage());
			log.error("the select link sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return link;
	}
}
